package com.space.config;
import com.alibaba.fastjson2.JSONObject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * sqlite操作类
 */
public class ConfigUtil {


    public static void createNewTable() {
        // SQL statement for creating a new table
        String sql = "CREATE TABLE IF NOT EXISTS configs (\n"
                + "    id integer PRIMARY KEY,\n"
                + "    data text \n"
                + ");";

        try (Connection conn = SqliteUtil.getConn();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void insert(String data, int id) {
        String sql = "INSERT INTO configs(id,data) VALUES(?,?)";

        try (Connection conn = SqliteUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.setString(2, data);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    public static String selectById(int id) {
        String sql = "SELECT data FROM configs where id =?";
        String value = "";


        try {
            PreparedStatement pstmt =  SqliteUtil.getConn().prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                // 假设 your_table 有列 column1, column2, ...
                value = rs.getString("data");
            } else {

            }

            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            value="erro";
        }

        return value;
    }

    public  static  List<JSONObject> selectAll() {
        String sql = "SELECT id, data FROM configs";

        List<JSONObject> all = new ArrayList<>();
        try (Connection conn = SqliteUtil.getConn();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // loop through the result set
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                obj.put("id",rs.getInt("id"));
                obj.put("data",rs.getString("data"));
                all.add(obj);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return all;
    }

    public static void update(int id, String data) {
        String sql = "UPDATE configs SET data = ?   "
                + "WHERE id = ?";

        try (Connection conn = SqliteUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setString(1, data);
            pstmt.setInt(2, id);
            // update
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void delete(int id) {
        String sql = "DELETE FROM configs WHERE id = ?";

        try (Connection conn = SqliteUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setInt(1, id);
            // execute the delete statement
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }



    public static void drop() {
        String sql = "drop table configs";

        try (Connection conn = SqliteUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        ConfigUtil app = new ConfigUtil();
        app.drop();
        app.createNewTable();
        app.insert("Alice", 30);
        app.insert("Bob", 25);
        System.out.println( app.selectAll());;
        app.update(30, "Alicia");
//        app.delete(25);
        System.out.println( app.selectAll());;
        System.out.println( app.selectById(25));;
    }
}
